﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using JKF.DataModule.IDAL;
using JKF.Entities.Model;
using JKF.Substructure;

namespace JKF.DataModule.SqlServerDAL
{
    /// <summary>
    /// 数据访问类dalSALES_CONSUME。
    /// </summary>
    public partial class dalSALES_CONSUME : IdalSALES_CONSUME
    {
        private static string FieldToShow = "SCON.CONSUME_KEY,SCON.UNIQUE_CODE,SCON.CUSTOMER_NAME,SCON.CUSTOMER_CARD_NO,SCON.CUSTOMER_MOBILE,SCON.CUSTOMER_PHONE,SCON.PRODUCT_NAME,SCON.PRODUCT_MODEL,SCON.WAY_CODE,SCON.REGISTER_DATE,SCON.[MONEY],SCON.ADD_POINT,SCON.STATUS_NAME AS STATUS,SCON.OPERATOR,SCON.MEMO,SCON.DATE_CREATE";
        private static string FromTable = "vl_sales_consume_view SCON";
        private static string FieldKey = "SCON.CONSUME_KEY";
        private static string FieldOrder = "SCON.DATE_UPDATE desc";

        /// <summary>
        /// 返回配件信息
        /// </summary>
        /// <param name="regStart"></param>
        /// <param name="regEnd"></param>
        /// <param name="status"></param>
        /// <param name="ownShops"></param>
        /// <param name="filter"></param>
        /// <param name="filterOp"></param>
        /// <param name="filterKey"></param>
        /// <param name="currentUser"></param>
        /// <param name="pageCurrent"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageCount"></param>
        /// <param name="rowCount"></param>
        /// <returns></returns>
        public static DataTable GetSalesConsumePaging(string regStart, string regEnd, string status, string ownShops,
            string filter, string filterOp, string filterKey, string currentUser, int pageCurrent, int pageSize, ref int pageCount, ref int rowCount)
        {
            var sqlBuilder = new StringBuilder();//used to join
            sqlBuilder.AppendFormat(" SCON.SHOP_CODE IN(SELECT SHOP_CODE FROM SYS_USER_SHOP WHERE USER_KEY='{0}') and SCON.STATUS='1'", currentUser);
            DateTime dtTmp;
            if (DateTime.TryParse(regStart, out dtTmp)) sqlBuilder.AppendFormat(" AND SCON.REGISTER_DATE>='{0}'", dtTmp.ToShortDateString());
            if (DateTime.TryParse(regEnd, out dtTmp)) sqlBuilder.AppendFormat(" AND SCON.REGISTER_DATE<='{0}'", dtTmp.ToShortDateString() + " 23:59:59");
            if (!string.IsNullOrEmpty(status)) sqlBuilder.AppendFormat(" AND SCON.STATUS='{0}'", status);
            if (!string.IsNullOrEmpty(ownShops)) sqlBuilder.AppendFormat(" AND SCON.SHOP_CODE in('{0}')", string.Join("','", ownShops.Split(',')));

            if (!string.IsNullOrEmpty(filterKey) && !string.IsNullOrEmpty(filterOp) && !string.IsNullOrEmpty(filter))
            {
                switch (filter.ToLower())
                {
                    case "card_no":
                        sqlBuilder.Append(BuildCondition("SCON.CUSTOMER_CARD_NO", filterKey, filterOp));
                        break;
                    case "customer_name":
                        sqlBuilder.Append(BuildCondition("SCON.CUSTOMER_NAME", filterKey, filterOp));
                        break;
                    case "mobile":
                        sqlBuilder.Append(BuildCondition("SCON.CUSTOMER_MOBILE", filterKey, filterOp));
                        break;
                    case "unique_code":
                        sqlBuilder.Append(BuildCondition("SCON.UNIQUE_CODE", filterKey, filterOp));
                        break;
                    case "product_name":
                        sqlBuilder.Append(BuildCondition("SCON.PRODUCT_NAME", filterKey, filterOp));
                        break;
                    case "model":
                        sqlBuilder.Append(BuildCondition("PR.PRODUCT_MODEL", filterKey, filterOp));
                        break;
                    case "memo":
                        sqlBuilder.Append(BuildCondition("SCON.[memo]", filterKey, filterOp));
                        break;
                    case "operator":
                        sqlBuilder.AppendFormat((filterOp == "="
                                                     ? " And SCON.USER_CREATE in (select USER_KEY from sys_user where real_name='{0}')"
                                                     : " And SCON.USER_CREATE in (select USER_KEY from sys_user where real_name like '%{0}%')"), filterKey);
                        break;
                }
            }
            var lockDay = dalSYS_PARAM_VALUE.GetSalesDataLockDays();
            return lockDay > 0 ? DataPage.GetPageInfo(FromTable, FieldKey, pageCurrent, pageSize,
                FieldToShow + string.Format(" , datediff(dd,dateadd(dd,{0},DATE_CREATE),getdate()) AS isLock ", lockDay), FieldOrder, sqlBuilder.ToString(), ref pageCount, ref rowCount).Tables[0] :
                DataPage.GetPageInfo(FromTable, FieldKey, pageCurrent, pageSize, FieldToShow + " ,-1 AS isLock ", FieldOrder, sqlBuilder.ToString(), ref pageCount, ref rowCount).Tables[0];
        }

        /// <summary>
        /// 返回当前客户购买记录
        /// </summary>
        /// <param name="customerKey"></param>
        /// <param name="currentUser"></param>
        /// <returns></returns>
        public static DataTable GetCustomerSalesConsume(string customerKey, string currentUser)
        {
            var sqlBuilder = new StringBuilder("select ");//used to join
            sqlBuilder.AppendFormat(" {0} from {1} where ", FieldToShow, FromTable);
            sqlBuilder.AppendFormat(" SCON.SHOP_CODE IN(SELECT SHOP_CODE FROM SYS_USER_SHOP WHERE USER_KEY='{0}') and SCON.STATUS='1'", currentUser);
            sqlBuilder.AppendFormat(" AND SCON.CUSTOMER_KEY='{0}'", customerKey);
            sqlBuilder.AppendFormat(" order by {0}", FieldOrder);
            return DB.HR.ExecuteDataSet(CommandType.Text, sqlBuilder.ToString()).Tables[0];
        }

        /// <summary>
        /// 通过客户编号返回客户配件耗材记录
        /// </summary>
        /// <param name="customerKey"></param>
        /// <param name="currentUser"></param>
        /// <param name="pageCurrent"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageCount"></param>
        /// <param name="rowCount"></param>
        /// <returns></returns>
        public static DataTable GetSalesConsumeForCustomerPaging(string customerKey, string currentUser, int pageCurrent,
                                                                 int pageSize, ref int pageCount, ref int rowCount)
        {
            var sqlBuilder = new StringBuilder();
            sqlBuilder.AppendFormat(" SCON.CUSTOMER_KEY='{0}'", customerKey);
            sqlBuilder.AppendFormat(" AND SCON.SHOP_CODE IN(SELECT SHOP_CODE FROM SYS_USER_SHOP WHERE USER_KEY='{0}')", currentUser);

            return DataPage.GetPageInfo(FromTable, FieldKey, pageCurrent, pageSize, FieldToShow, FieldOrder, sqlBuilder.ToString(),
                ref pageCount, ref rowCount).Tables[0];
        }

        /// <summary>
        /// 删除配件耗材维修更换记录
        /// </summary>
        /// <param name="consumeKey"></param>
        /// <returns></returns>
        public static int RemoveSalesConsume(string consumeKey)
        {
            var rslt = 0;
            var db = DB.HR;
            var dbCommand = db.GetStoredProcCommand("proc_sales_consume_delete");
            db.AddInParameter(dbCommand, "@CONSUME_KEY", DbType.Guid, consumeKey.ToGuid());
            db.AddOutParameter(dbCommand, "@rslt", DbType.Int32, rslt);
            var ds = db.ExecuteNonQuery(dbCommand);
            return int.Parse(dbCommand.Parameters["@rslt"].Value.ToString());
        }

        /// <summary>
        /// 添加配件耗材维修更换记录
        /// </summary>
        /// <param name="consume"></param>
        /// <param name="pointlog"></param>
        /// <param name="stockOut"></param>
        /// <returns></returns>
        public static bool InsertSalesComsume(SALES_CONSUMEinfo consume, POINT_LOGinfo pointlog, STOCK_OUTinfo stockOut)
        {
            var rslt = 1;
            var db = DB.HR;
            var dbCommand = db.GetStoredProcCommand("proc_sales_consume_insert");
            db.AddInParameter(dbCommand, "@CONSUME_KEY", DbType.Guid, consume.CONSUME_KEY);
            db.AddInParameter(dbCommand, "@PRODUCT_KEY", DbType.Guid, consume.PRODUCT_KEY);
            db.AddInParameter(dbCommand, "@UNIQUE_CODE", DbType.String, consume.UNIQUE_CODE);
            db.AddInParameter(dbCommand, "@CUSTOMER_KEY", DbType.Guid, consume.CUSTOMER_KEY);
            db.AddInParameter(dbCommand, "@WAY_CODE", DbType.String, consume.WAY_CODE);
            if (consume.REGISTER_DATE != null) db.AddInParameter(dbCommand, "@REGISTER_DATE", DbType.DateTime, consume.REGISTER_DATE.Value);
            db.AddInParameter(dbCommand, "@MONEY", DbType.Decimal, consume.MONEY.HasValue ? consume.MONEY.Value : 0);
            db.AddInParameter(dbCommand, "@ADD_POINT", DbType.Int32, pointlog.POINT_VALUE.HasValue ? pointlog.POINT_VALUE : 0);
            db.AddInParameter(dbCommand, "@POINT_KEY", DbType.Guid, pointlog.POINT_KEY);
            db.AddInParameter(dbCommand, "@MEMO", DbType.String, consume.MEMO);
            //db.AddInParameter(dbCommand, "@POINT_CODE", DbType.String, pointlog.CODE);
            db.AddInParameter(dbCommand, "@POINTWAY", DbType.String, pointlog.WAY);
            db.AddInParameter(dbCommand, "@POINTCODE", DbType.String, pointlog.CODE);
            db.AddInParameter(dbCommand, "@OUT_KEY", DbType.Guid, stockOut.OUT_KEY);
            db.AddInParameter(dbCommand, "@SHOP_CODE", DbType.String, stockOut.SHOP_CODE);
            db.AddInParameter(dbCommand, "@USER_CREATE", DbType.Guid, stockOut.USER_CREATE);
            db.AddInParameter(dbCommand, "@OUT_NO", DbType.String, stockOut.OUT_NO);
            db.AddOutParameter(dbCommand, "@rslt", DbType.Int32, rslt);
            var ds = db.ExecuteNonQuery(dbCommand);
            rslt = int.Parse(dbCommand.Parameters["@rslt"].Value.ToString());
            return rslt == 1;
        }

        /// <summary>
        /// 获得销售编号
        /// </summary>
        /// <returns></returns>
        public static string GetSalesConsumeUniqueCode(string shopCode)
        {
            var uniqueCodePerfix = string.Format("hc{0}", shopCode);
            var db = DB.HR;

            var result = db.ExecuteDataSet(CommandType.Text,
                              string.Format(
                                  "select top 1 unique_code from sales_consume where unique_code like '{0}%' order by unique_code desc",
                                  uniqueCodePerfix)).Tables[0];
            if (result != null && result.Rows.Count > 0)
            {
                var num = result.Rows[0][0].ToString();
                var tmpIndex = 0;
                tmpIndex = int.Parse(num.Replace(uniqueCodePerfix, ""));
                return string.Format("{0}{1}", uniqueCodePerfix, (tmpIndex + 1).ToString().PadLeft(9, '0'));
            }
            return string.Format("{0}000000000", uniqueCodePerfix);
        }

        /// <summary>
        /// 更新配件耗材维修记录
        /// </summary>
        /// <param name="consume"></param>
        /// <returns></returns>
        public static bool UpdateSalesConsume(SALES_CONSUMEinfo consume)
        {
            var rslt = 1;
            var db = DB.HR;
            var dbCommand = db.GetStoredProcCommand("proc_sales_consume_update");
            db.AddInParameter(dbCommand, "@CONSUME_KEY", DbType.Guid, consume.CONSUME_KEY);
            db.AddInParameter(dbCommand, "@PRODUCT_KEY", DbType.Guid, consume.PRODUCT_KEY);
            db.AddInParameter(dbCommand, "@WAY_CODE", DbType.String, consume.WAY_CODE);
            if (consume.REGISTER_DATE != null) db.AddInParameter(dbCommand, "@REGISTER_DATE", DbType.Date, consume.REGISTER_DATE);
            db.AddInParameter(dbCommand, "@MONEY", DbType.Decimal, consume.MONEY);
            db.AddInParameter(dbCommand, "@ADD_POINT", DbType.Int32, consume.ADD_POINT.HasValue ? consume.ADD_POINT.Value : 0);
            db.AddInParameter(dbCommand, "@MEMO", DbType.String, consume.MEMO);
            db.AddOutParameter(dbCommand, "@rslt", DbType.Int32, rslt);
            var ds = db.ExecuteNonQuery(dbCommand);
            rslt = int.Parse(dbCommand.Parameters["@rslt"].Value.ToString());
            return rslt == 1;
        }

        private static string BuildCondition(string field, string val, string operater)
        {
            return string.Format((operater == "=" ? " And {0}='{1}'" : " And {0} like '%{1}%'"), field, val);
        }
    }
}
